import logging
from datetime import datetime
from typing import List, Optional

from fastapi import APIRouter, Query, Depends, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_elealarm_router = APIRouter(prefix="/alarmSet", tags=["用电报警设定管理"])


class EleSearch(PaginationBase):
    fsiteNo: str = Field(None, description="站点编号")


# todo 11.用电报警设置
@data_elealarm_router.get("/eleSet/list", summary="查询用电报警设定")
def list_elealarm_data(ele: EleSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from s_ts_smartwarring where 1=1"
    if ele.fsiteNo is not None:
        sql = sql + " and fsite_no= :fsiteNo "
        param["fsiteNo"] = ele.fsiteNo
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fwarringno,fsite_no,fdate_flag,fscore_flag,"
    sql = sql + " fbegnum,fendnum,fdescribe,flevelno,fsendmsg,"
    sql = sql + " fsendemail,fstate,foperator,fupddate"
    sql = sql + " from s_ts_smartwarring where 1=1"
    if ele.fsiteNo is not None:
        sql = sql + " and fsite_no= :fsiteNo "
        param["fsiteNo"] = ele.fsiteNo
    sql = sql + " limit :limit offset :offset "
    param["offset"] = ele.offset
    param["limit"] = ele.limit
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fwarringno,b.fentryid,b.fpoint_no fpointno,c.fpoint_name fpointName"
    sql_3 = sql_3 + " from s_ts_smartwarring a,s_ts_smartwarring_point b,a_ts_point c "
    sql_3 = sql_3 + " where a.fwarringno=b.fwarringno "
    sql_3 = sql_3 + " and b.fpoint_no=c.fpoint_no"
    if ele.fsiteNo is not None:
        sql_3 = sql_3 + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = ele.fsiteNo
    sql_3 = sql_3 + " order by b.fwarringno,b.fentryid"
    data_detail = db.execute(text(sql_3), param).fetchall()

    # userlist 明细列表
    param = {}
    sql_2 = " select b.fwarringno,b.fentryid,b.fuserno,c.username"
    sql_2 = sql_2 + " from s_ts_smartwarring a,s_ts_smartwarring_user b,am_sys_user c "
    sql_2 = sql_2 + " where a.fwarringno=b.fwarringno "
    sql_2 = sql_2 + " and b.fuserno=c.id::text"
    if ele.fsiteNo is not None:
        sql_2 = sql_2 + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = ele.fsiteNo
    sql_2 = sql_2 + " order by b.fwarringno,b.fentryid"
    data_userlist = db.execute(text(sql_2), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fwarringno'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fdateFlag'] = data[key][2]
        d1['fscoreFlag'] = data[key][3]
        d1['fbegnum'] = data[key][4]
        d1['fendnum'] = data[key][5]
        d1['fdescribe'] = data[key][6]
        d1['flevelno'] = data[key][7]
        d1['fsendmsg'] = data[key][8]
        d1['fsendemail'] = data[key][9]
        d1['fstate'] = data[key][10]
        d1['foperator'] = data[key][11]
        d1['fupddate'] = data[key][12]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fwarringno'] = data_detail[j1][0]
                d2['fentryid'] = data_detail[j1][1]
                d2['fpointno'] = data_detail[j1][2]
                d2['fpointName'] = data_detail[j1][3]
                detail_list.append(d2)

        detail_list2 = []
        for j2 in range(len(data_userlist)):
            if (str(data_userlist[j2][0]) == str(sbillno)):
                d3 = {}
                d3['fwarringno'] = data_userlist[j2][0]
                d3['fentryid'] = data_userlist[j2][1]
                d3['fuserno'] = data_userlist[j2][2]
                d3['username'] = data_userlist[j2][3]
                detail_list2.append(d3)

        d1["atsPoints"] = detail_list
        d1["atsUsers"] = detail_list2
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": ele.limit,
                 "page": ele.pageSize,
                 "pages": (count + ele.pageSize - 1) // ele.pageSize,
                 "total": count
                 }
    return BaseResponse.success(data=info_dict)


class SmartWarringCreate(BaseModel):
    """用电报警设定创建模型"""
    fsiteNo: str = Field(..., description="站点编号")
    fscoreFlag: int = Field(..., description="分数标识（1:启用，0:停用）")
    fdateFlag: int = Field(..., description="日期标识（1:按天，0:按周期）")
    fbegnum: float = Field(..., description="起始数值")
    fendnum: float = Field(..., description="结束数值")
    fdescribe: str = Field(..., description="报警描述")
    flevelno: int = Field(..., description="报警级别（1-5级）")
    fsendmsg: int = Field(..., description="是否发送短信（1:是，0:否）")
    fsendemail: int = Field(..., description="是否发送邮件（1:是，0:否）")
    fstate: int = Field(1, description="报警状态（1:启用，0:停用）")

    # 嵌套子模型
    atsUsers: List[int] = Field(..., description="用户ID列表")
    atsPoints: List[str] = Field(..., description="教室点位编号列表")

    # 自动填充字段（由业务层处理）
    fwarringno: Optional[int] = Field(None, description="报警单号（自动生成）")
    foperator: Optional[str] = Field(None, description="操作人ID")
    fupddate: Optional[datetime] = Field(None, description="更新时间")


class WarringUserCreate(BaseModel):
    """报警关联用户模型"""
    fwarringno: int = Field(..., description="报警单号")
    fuserno: int = Field(..., description="用户ID")


class WarringPointCreate(BaseModel):
    """报警关联教室模型"""
    fwarringno: int = Field(..., description="报警单号")
    fpoint_no: str = Field(..., description="教室点位编号")


@data_elealarm_router.post("/eleSet/create", summary="新增用电报警设定")
def create_elealarm_data(smart: SmartWarringCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = 3
        if token is not None:
            user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            fwarringno = 0
            sql = "select last_value+1  last_value from s_ts_smartwarring_fwarringno_seq"
            res = db.execute(text(sql)).fetchall()
            fwarringno = res[0][0]
            # 返回内容
            d1 = {}
            sql = "insert into s_ts_smartwarring(fwarringno,fsite_no,fscore_flag,fdate_flag,fbegnum,fendnum,fdescribe,flevelno,fsendmsg,fsendemail,fstate,foperator,fupddate)"
            sql = sql + " values( :fwarringno , :fsiteNo , :fscoreFlag , :fdateFlag , :fbegnum , :fendnum , :fdescribe , :flevelno , :fsendmsg , :fsendemail , :fstate , :userid ,now());"
            db.execute(text(sql), {
                "fwarringno": fwarringno,
                "fsiteNo": smart.fsiteNo,
                "fscoreFlag": smart.fscoreFlag,
                "fdateFlag": smart.fdateFlag,
                "fbegnum": smart.fbegnum,
                "fendnum": smart.fendnum,
                "fdescribe": smart.fdescribe,
                "flevelno": smart.flevelno,
                "fsendmsg": smart.fsendmsg,
                "fsendemail": smart.fsendemail,
                "fstate": smart.fstate,
                "userid": user_id
            })

            # 人员列表
            Users = []

            Users = smart.atsUsers
            for key in Users:
                sql = "insert into s_ts_smartwarring_user(fwarringno,fuserno)"
                sql = sql + " values( :fwarringno , :fuserno  );"
                db.execute(text(sql), {
                    "fwarringno": fwarringno,
                    "fuserno": key,
                })

            # 教室列表
            Points = []

            Points = smart.atsPoints

            for key in Points:
                sql = "insert into s_ts_smartwarring_point(fwarringno,fpoint_no)"
                sql = sql + " values( :fwarringno , :fpointNo );"
                db.execute(text(sql), {
                    "fwarringno": fwarringno,
                    "fpointNo": key,
                })

            # 返回
            d1["fdateFlag"] = smart.fdateFlag
            d1["fbegnum"] = smart.fbegnum
            d1["fdescribe"] = smart.fdescribe
            d1["fendnum"] = smart.fendnum
            d1["flevelno"] = smart.flevelno
            d1["fscoreFlag"] = smart.fscoreFlag
            d1["fsendemail"] = smart.fsendemail
            d1["fsendmsg"] = smart.fsendmsg
            d1["fsiteNo"] = smart.fsiteNo
            d1["fstate"] = smart.fstate
            d1["fwarringno"] = fwarringno
            d1["fupddate"] = None
            d1["atsUsers"] = Users
            d1["atsPoints"] = Points
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_elealarm_router.post("/eleSet/update", summary="修改用电报警设定")
def update_elealarm_data(smart: SmartWarringCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "update s_ts_smartwarring set fscore_flag= :fscoreFlag "
            sql = sql + ",fdate_flag= :fdateFlag ,fbegnum= :fbegnum ,fendnum= :fendnum "
            sql = sql + ",fdescribe= :fdescribe ,flevelno= :flevelno ,fsendmsg= :fsendmsg "
            sql = sql + ",fsendemail= :fsendemail ,fstate= :fstate "
            sql = sql + ",foperator= :userid ,fupddate=now()"
            sql = sql + " where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fscoreFlag": smart.fscoreFlag,
                "fdateFlag": smart.fdateFlag,
                "fbegnum": smart.fbegnum,
                "fendnum": smart.fendnum,
                "fdescribe": smart.fdescribe,
                "flevelno": smart.flevelno,
                "fsendmsg": smart.fsendmsg,
                "fsendemail": smart.fsendemail,
                "fstate": smart.fstate,
                "userid": user_id,
                "fwarringno": smart.fwarringno,
            })
            # 人员列表
            sql = "delete from s_ts_smartwarring_user where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": smart.fwarringno,
            })
            Users = smart.atsUsers
            for fuserno in Users:
                sql = "insert into s_ts_smartwarring_user(fwarringno,fuserno)"
                sql = sql + " values( :fwarringno , :fuserno );"
                db.execute(text(sql), {
                    "fwarringno": smart.fwarringno,
                    "fuserno": fuserno,
                })

            # 教室列表
            sql = "delete from s_ts_smartwarring_point where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": smart.fwarringno,
            })
            Points = smart.atsPoints
            for fpointNo in Points:
                sql = "insert into s_ts_smartwarring_point(fwarringno,fpoint_no)"
                sql = sql + " values( :fwarringno , :fpointNo );"
                db.execute(text(sql), {
                    "fwarringno": smart.fwarringno,
                    "fpointNo": fpointNo
                })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_elealarm_router.post("/eleSet/delete/{fwarringno}", summary="删除用电报警设定")
def delete_elealarm_data(fwarringno: int = Path(), db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        with db.begin():
            sql = "delete from s_ts_smartwarring where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": fwarringno
            })

            sql = "delete from s_ts_smartwarring_point where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": fwarringno
            })

            sql = "delete from s_ts_smartwarring_user where fwarringno= :fwarringno "
            db.execute(text(sql), {
                "fwarringno": fwarringno
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


class EleSearch(PaginationBase):
    begdate: datetime = Field("2000-01-01", description="开始时间")
    enddate: datetime = Field("2000-01-01", description="结束时间")
    fsiteNo: str = Field(None, description="客户编号")
    fairitem: str = Field(None, description="")


@data_elealarm_router.get("/eleAlarm/list", summary="查询用电报警")
def get_elealarm_list(ele: EleSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from s_tc_smartwarring a"
    sql = sql + " left join a_ts_site b on a.fsite_no=b.fsite_no"
    sql = sql + " left join a_ts_point c on a.fpoint_no=c.fpoint_no"
    sql = sql + " left join a_ts_site d on b.fsite_parent=d.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fgetdate,'yyyymmdd') between :begdate "
    sql = sql + " and  :enddate "
    param["begdate"] = ele.begdate
    param["enddate"] = ele.enddate
    if ele.fsiteNo is not None:
        sql = sql + " and a.fsite_no= :fsiteNo "
        param['fsiteNo'] = ele.fsiteNo
    if ele.fairitem is not None:
        sql = sql + " and a.fairitem= :fairitem "
        param['fairitem'] = ele.fairitem
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fgetdate,d.fsite_name as fareaName,b.fsite_name,c.fpoint_name"
    sql = sql + ",a.fcurnum,a.fbegnum,a.fendnum,a.fdescribe"
    sql = sql + ",case a.fdate_flag when 1 then '天' else '月' end fdate_flag"
    sql = sql + ",case a.fscore_flag when 1 then '学校' else '教室' end fscore_flag"
    sql = sql + ",case a.fsendmsg when 0 then '未发送' else '已发送' end fsendmsg"
    sql = sql + ",case a.fsendemail when 0 then '未发送' else '已发送' end fsendemail"
    sql = sql + " from s_tc_smartwarring a"
    sql = sql + " left join a_ts_site b on a.fsite_no=b.fsite_no"
    sql = sql + " left join a_ts_point c on a.fpoint_no=c.fpoint_no"
    sql = sql + " left join a_ts_site d on b.fsite_parent=d.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fgetdate,'yyyymmdd') between :begdate "
    sql = sql + " and  :enddate "
    param["begdate"] = ele.begdate
    param["enddate"] = ele.enddate
    if ele.fsiteNo is not None:
        sql = sql + " and a.fsite_no= :fsiteNo "
        param['fsiteNo'] = ele.fsiteNo
    if ele.fairitem is not None:
        sql = sql + " and a.fairitem= :fairitem "
        param['fairitem'] = ele.fairitem
    sql = sql + " limit :limit offset :offset "
    param["limit"] = ele.limit
    param["offset"] = ele.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['begdate'] = None
        d1['email'] = None
        d1['enddate'] = None
        d1['fareaName'] = data[key][1]
        d1['fbegnum'] = data[key][5]
        d1['fcurnum'] = data[key][4]
        d1['fdateFlag'] = None
        d1['fdateFlagStr'] = data[key][8]
        d1['fdescribe'] = data[key][7]
        d1['fendnum'] = data[key][6]
        d1['fentryid'] = None
        d1['fgetdate'] = data[key][0]
        d1['fpointName'] = data[key][3]
        d1['fPointNo'] = None
        d1['fscoreFlag'] = None
        d1['fscoreFlagStr'] = data[key][8]
        d1['fsendemail'] = None
        d1['fsendemailStr'] = data[key][10]
        d1['fsendmsg'] = None
        d1['fsendmsgStr'] = data[key][9]
        d1['fsiteName'] = data[key][2]
        d1['fsiteNo'] = None
        d1['fwarringno'] = None
        d1['phone'] = None
        data_list.append(d1)

    info_dict = {"list": data_list, "count": count}
    return BaseResponse.success(data=info_dict)


@data_elealarm_router.get("/eleSet/sTsAiritemOptions", summary="查询空气字典")
def list_air_item(db: Session = Depends(get_db)):
    sql = "select fairitem as vaule,fariname as label from s_ts_airitem where 1=1 order by fairitem"

    data = db.execute(text(sql)).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['vaule'] = data[key][0]
        d1['label'] = data[key][1]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@data_elealarm_router.get("/eleSet/listUserByFsiteNo/{id}", summary="获取属于该组织的用户")
def list_get_user(id: int = Path(), db: Session = Depends(get_db)):
    sql = "select id fuserno,username from am_sys_user "
    sql = sql + " where fsite_no_belong= :id "
    sql = sql + " and status=1"
    sql = sql + " order by id"
    roledata = db.execute(text(sql), {
        "id": id,
    }).fetchall()
    data_list = []
    for key in range(len(roledata)):
        d2 = {}
        d2['fuserno'] = roledata[key][0]
        d2['username'] = roledata[key][1]
        data_list.append(d2)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@data_elealarm_router.get("/eleSet/listPointByFsiteNo/{id}", summary="获取属于该组织的教室")
def list_get_point(id: int = Path(), db: Session = Depends(get_db)):
    sql = "select fpoint_no as fpointNo,fpoint_name as fpointName from a_ts_point "
    sql = sql + " where fsite_no= :id "
    sql = sql + " and fpoint_flag=0"
    sql = sql + " and fpoint_state=1"
    sql = sql + " order by fpoint_no"
    roledata = db.execute(text(sql), {
        "id": id,
    }).fetchall()
    data_list = []
    for key in range(len(roledata)):
        d2 = {}
        d2['fpointNo'] = roledata[key][0]
        d2['fpointName'] = roledata[key][1]
        data_list.append(d2)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)
